package com.juzipi.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.juzipi.demo.pojo.Student;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface StudentMapper extends BaseMapper<Student> {

    //将所有的 deleted 值重置为 0，就实现了将逻辑删除的数据恢复
    @Update("update student set deleted = 0")
    Integer resetStudent();

    //这个查询是查询最近七天新增数据的次数
    //我也看不太懂，但是感觉不复杂
    @Select("select a.click_date ,ifnull(b.count,0) as count\n" +
            "from (\n" +
            "    select curdate() as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 1 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 2 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 3 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 4 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 5 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 6 day) as click_date\n" +
            "         ) a left join (\n" +
            "             select date(create_time) as datetime,count(*) as count from student\n" +
            "             group by date(create_time)\n" +
            "    )b on a.click_date = b.datetime")
    List<Map<Object, Object>> queryStudentNewData();


    //有思路了，如法炮制，给数据库加一个deleteTime字段，默认值为null，在删除的时候给这个字段赋值
    //然后就可以根据这个时间查了
    @Select("select a.click_date ,ifnull(b.count,0) as count\n" +
            "from (\n" +
            "    select curdate() as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 1 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 2 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 3 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 4 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 5 day) as click_date\n" +
            "    union all\n" +
            "    select date_sub(curdate(),interval 6 day) as click_date\n" +
            "         ) a left join (\n" +
            "             select date(delete_time) as deleteTime,count(*) as count from student\n" +
            "             group by date(delete_time)\n" +
            "    )b on a.click_date = b.deleteTime;")
    List<Map<Object, Object>> queryStudentDeleteCount();
}
